import xlwings as xw
from xlwings.utils import rgb_to_int


class ExcelHelper(object):
    def __init__(self, fullname="text3.xlsx", sheet="Sheet1", visible=True):
        app = xw.App(visible=visible, add_book=False)  # 界面设置
        app.display_alerts = visible  # 提示信息
        app.screen_updating = visible  # 显示更新

        self.wb = app.books.open(fullname)
        self.sht = self.wb.sheets[sheet]  # 实例化工作表

    def save(self, fullname="result.xlsx"):
        self.wb.save(fullname)

    def close(self):
        self.wb.close()

    def set_border(self, cell1=None, cell2=None, linestyle=1, weight=2, color=(0, 0, 0),
                   edge=[7, 8, 9, 10, 11, 12]):
        """设置边框

        :param cell1: 设置范围左上角单元格。默认为全局
        :param cell2: 设置范围右下角单元格。默认为全局
        :param linestyle: 边框线型。1直线 2虚线 4点划线 5双点划线
        :param weight: 边框粗细。默认为2
        :param color: 边框颜色。默认为黑色
        :param edge: 边框位置。默认为内部线，5左上角对角线 6左下角对角线 7左 8上 9下 10右 11内部垂直线 12内部水平线
        """
        if all([cell1, cell2]):
            _range = self.sht.range(cell1, cell2)
        else:
            # _range = self.sht.range("A1", self.sht.used_range.last_cell)  # 用过的最后一个单元格
            _range = self.sht.range("A1", "XFD1048576")  # 全局单元格
        for i in edge:
            _range.api.Borders(i).LineStyle = linestyle
            _range.api.Borders(i).Weight = weight
            _range.api.Borders(i).Color = rgb_to_int(color)


if __name__ == "__main__":
    excelHelper = ExcelHelper()
    excelHelper.set_border(color=(0, 0, 0))
    excelHelper.save('result2.xlsx')
    input()
    excelHelper.close()
